{smcl}
{com}{sf}{ul off}{txt}{.-}
      name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\Micah\Dropbox\Irrigation technology transition\final revisions for conditional acceptance\replication materials\outputs\logs\prepare_wrg_dataset.smcl
  {txt}log type:  {res}smcl
 {txt}opened on:  {res}19 May 2024, 20:19:29
{txt}
{com}. 
. *Bring in WIMAS data for 1991-2019
. use "$dr_data\raw\wimas_1991_2019.dta", clear
{txt}
{com}.         *Keep only records for which the use made of water is irrigation
.         keep if umw_code=="IRR"
{txt}(228,539 observations deleted)

{com}. 
. *Merge with SSURGO
. merge 1:1 wuadet_key using "$dr_data\raw\ssurgo.dta", generate(ssurgo_merge)
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          36,011
{txt}{col 9}from master{col 30}{res}           5,574{txt}  (ssurgo_merge==1)
{col 9}from using{col 30}{res}          30,437{txt}  (ssurgo_merge==2)

{col 5}Matched{col 30}{res}         989,397{txt}  (ssurgo_merge==3)
{col 5}{hline 41}

{com}. 
. *Merge with PRISM
. merge 1:1 wuadet_key using "$dr_data\raw\prism_monthly.dta", generate(prism_merge)
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          76,923
{txt}{col 9}from master{col 30}{res}          76,880{txt}  (prism_merge==1)
{col 9}from using{col 30}{res}              43{txt}  (prism_merge==2)

{col 5}Matched{col 30}{res}         948,528{txt}  (prism_merge==3)
{col 5}{hline 41}

{com}. 
. /* Construct additional variables using PRISM and WIMAS variables specific at the wuadet_key level
>                 NOTE - WUADET_KEY currently uniquely identifies records in this dataset. 
> */
. 
. ********************************************************************************
. ***************** Destring some WIMAS variables ********************************
. ********************************************************************************
.         *Destring some WIMAS variables 
.         foreach var of varlist type_system acres_irr dpth_water dpth_well gmd {c -(}
{txt}  2{com}.                 destring `var', replace 
{txt}  3{com}.         {c )-}
{txt}type_system already numeric; no {res}replace
{txt}acres_irr already numeric; no {res}replace
{txt}dpth_water already numeric; no {res}replace
{txt}dpth_well already numeric; no {res}replace
{txt}gmd already numeric; no {res}replace
{txt}
{com}. 
. ********************************************************************************
. ***************** Create irrigation specific variables  ***************
. ********************************************************************************
. *Create acre-feet used for irrigation variable
.         gen af_used_irr = af_used if umw_code=="IRR"
{txt}(30,480 missing values generated)

{com}. 
. *Create crop specific acreage variables 
.         *Start with corn
.         gen corn_acres = acres_irr if crop_code==2
{txt}(784,534 missing values generated)

{com}.                 replace corn_acres = .5*acres_irr if crop_code==18 | inrange(crop_code, 23, 26)
{txt}(98,341 real changes made)

{com}.                 replace corn_acres = (1/3)*acres_irr if inrange(crop_code, 33,36) | ///
>                         inrange(crop_code, 43,48)
{txt}(18,970 real changes made)

{com}.                 replace corn_acres = (1/4)*acres_irr if inrange(crop_code, 53, 58) | ///
>                         inrange(crop_code, 63,66) | inrange(crop_code, 68, 69)
{txt}(2,047 real changes made)

{com}.                 replace corn_acres = (1/5)*acres_irr if inrange(crop_code, 70, 71) | inrange(crop_code, 73, 74)
{txt}(80 real changes made)

{com}. 
.         *Now do soybeans hist 
.         gen soy_acres = acres_irr if crop_code==4
{txt}(973,096 missing values generated)

{com}.                 replace soy_acres = .5*acres_irr if crop_code==20 | crop_code==24 | ///
>                         crop_code==27 | inrange(crop_code, 30, 31)
{txt}(41,477 real changes made)

{com}.                 replace soy_acres = (1/3)*acres_irr if crop_code==34 | crop_code==37 | ///
>                         inrange(crop_code, 40,41) | crop_code==43 | inrange(crop_code, 46,47) | ///
>                         inrange(crop_code, 49,50) | crop_code==52
{txt}(8,099 real changes made)

{com}.                 replace soy_acres = (1/4)*acres_irr if crop_code==53 | ///
>                         inrange(crop_code, 56,57) | inrange(crop_code, 59,60) | ///
>                         inrange(crop_code, 62,64) | inrange(crop_code, 66,68)
{txt}(1,352 real changes made)

{com}.                 replace soy_acres = (1/5)*acres_irr if inrange(crop_code, 71, 74)
{txt}(86 real changes made)

{com}.                 
.                 *Now do wheat
.         gen wheat_acres = acres_irr if crop_code==5
{txt}(1,003,051 missing values generated)

{com}.                 replace wheat_acres = .5*acres_irr if crop_code==21 | crop_code==25 | ///
>                         crop_code==28 | crop_code==30 | crop_code==32
{txt}(67,151 real changes made)

{com}.                 replace wheat_acres = (1/3)*acres_irr if crop_code==35 | crop_code==38 | ///
>                         crop_code==40 | crop_code==42 | crop_code==44 | crop_code==46 | ///
>                         inrange(crop_code, 48, 49) | crop_code==51 | crop_code==52
{txt}(18,672 real changes made)

{com}.                 replace wheat_acres = (1/4)*acres_irr if crop_code==54 | ///
>                         inrange(crop_code, 59, 61) | inrange(crop_code, 61,63) | ///
>                         inrange(crop_code, 65, 67) | crop_code==69
{txt}(1,791 real changes made)

{com}.                 replace wheat_acres = (1/5)*acres_irr if inrange(crop_code, 70, 74)
{txt}(96 real changes made)

{com}.                 
.         *Now do sorghum
.         gen sorghum_acres = acres_irr if crop_code==3
{txt}(1,006,961 missing values generated)

{com}.                 replace sorghum_acres = .5*acres_irr if crop_code==19 | crop_code==23 | ///
>                         crop_code==27 | crop_code==28 | crop_code==29
{txt}(17,707 real changes made)

{com}.                 replace sorghum_acres = (1/3)*acres_irr if crop_code==33 | inrange(crop_code, 37, 39) | ///
>                         inrange(crop_code, 43, 45) | inrange(crop_code, 49, 51) 
{txt}(11,062 real changes made)

{com}.                 replace sorghum_acres = (1/4)*acres_irr if inrange(crop_code, 53, 55) | ///
>                         crop_code==56 | inrange(crop_code, 58,59) | inrange(crop_code, 63,65) | ///
>                         inrange(crop_code, 67, 69)
{txt}(2,057 real changes made)

{com}.                 replace sorghum_acres = (1/5)*acres_irr if crop_code==70 | inrange(crop_code, 72, 74)
{txt}(89 real changes made)

{com}.                 
.         *Now do alfalfa
.         gen alfalfa_acres = acres_irr if crop_code==1
{txt}(980,630 missing values generated)

{com}.                 replace alfalfa_acres = .5*acres_irr if inrange(crop_code, 18, 22) 
{txt}(11,464 real changes made)

{com}.                 replace alfalfa_acres = (1/3)*acres_irr if inrange(crop_code, 33, 42)
{txt}(4,903 real changes made)

{com}.                 replace alfalfa_acres = (1/4)*acres_irr if inrange(crop_code, 53, 62) | inrange(crop_code, 68, 69)
{txt}(1,069 real changes made)

{com}.                 replace alfalfa_acres = (1/5)*acres_irr if inrange(crop_code, 70, 72)
{txt}(33 real changes made)

{com}.                 replace alfalfa_acres = (1/6)*acres_irr if crop_code==74
{txt}(21 real changes made)

{com}. 
. *Create irrigation technology specific acreage variables. Only do flood, cp, and LEPA
.         *Start with flood irrigated acres
.         gen flood_acres = acres_irr if type_system==1   
{txt}(910,804 missing values generated)

{com}.         *Create center-pivot irrigated acres, do not include "CP and flood" option (type_system==6)
.         gen cp_acres = acres_irr if type_system==3
{txt}(887,506 missing values generated)

{com}.         *Now do center pivot with LEPA nozzles 
.         gen lepa_acres = acres_irr if type_system==4
{txt}(666,337 missing values generated)

{com}.                 
. ********************************************************************************
. ****************** PRISM and SSURGO ********************************************
. ********************************************************************************
. 
. ***********************  Generate evapotranspiration  **************************
.                 *Change latitude to radians 
.                 replace latitude = latitude*_pi/180
{txt}(994,971 real changes made)

{com}.                 * G, u, gamma
.                 local G 0
{txt}
{com}.                 local u 2
{txt}
{com}.                 
.                 * gamma with elevation from gSSURGO
.                 gen gamma_elev=0.000665*101.3*((293-0.0065*elev)/293)^5.26
{txt}(42,991 missing values generated)

{com}.                 
.                 ************
.                 * Radiation
.                 ************
.                 * extraterrestrial radiation (ra) 
.                 gen base_date=mdy(1,1,wua_year)
{txt}(30,480 missing values generated)

{com}.                 local month_num = 1
{txt}
{com}.                 local month_abbrevs "jan feb mar apr may jun jul aug sep oct nov dec"
{txt}
{com}.                 foreach month of local month_abbrevs {c -(}
{txt}  2{com}.                         * Generate day of the year (J)
.                         gen `month'_current_date=mdy(`month_num', 15, wua_year)
{txt}  3{com}.                         gen `month'_J = `month'_current_date - base_date + 1
{txt}  4{com}.                         
.                         * radiation
.                         gen `month'_dr= 1 + 0.033*cos(2*_pi*`month'_J/365)
{txt}  5{com}.                         gen `month'_delta= 0.409*sin(2*_pi*`month'_J/365-1.39)
{txt}  6{com}.                         gen `month'_pre_omega=-tan(latitude)*tan(`month'_delta)
{txt}  7{com}.                         /*This is where the problem is. Arccossign is only defined [-1,1] */
.                         gen `month'_omega=acos(`month'_pre_omega)
{txt}  8{com}.                         
.                         * Average temperature
.                         gen `month'_tavg = (`month'_tmin + `month'_tmax)/2
{txt}  9{com}.                         
.                         * vapour pressure deficit
.                         gen `month'_vpd=0.5*0.6108*(exp(17.27*`month'_tmax/(`month'_tmax+237.3))-exp(17.27*`month'_tmin/(`month'_tmin+237.3)))
{txt} 10{com}.                         
.                         * slope of vapour pressure curve
.                         gen `month'_Delta=(4098*0.6108*exp(17.27*`month'_tavg/(`month'_tavg+237.3)))/((`month'_tavg+237.3)^2)
{txt} 11{com}.                         
.                         * RA in mm/day
.                         gen `month'_Ra=24*60/_pi*0.0820*`month'_dr*(`month'_omega*sin(latitude)*sin(`month'_delta) + cos(latitude)*cos(`month'_delta)*sin(`month'_omega))
{txt} 12{com}. 
.                         gen `month'_Rns=(1-0.23)*0.16*(`month'_tmax-`month'_tmin)^(1/2)*`month'_Ra
{txt} 13{com}.                         gen `month'_Rnl=4.903*10^-9*(((`month'_tmax+273.16)^4+(`month'_tmin+273.16)^4)/2)* ///
>                                 (0.34-0.14*(0.6108*exp(17.27*`month'_tmin/(`month'_tmin+237.3)))^(1/2))* ///
>                                 (1.35*(0.16*(`month'_tmax-`month'_tmin)^(1/2)*`month'_Ra)/((0.75+2*10^-5)*`month'_Ra)-0.35)
{txt} 14{com}.                         gen `month'_Rn=`month'_Rns-`month'_Rnl
{txt} 15{com}. 
.                         gen `month'_ET0_elev=(0.408*`month'_Delta*(`month'_Rn-`G')+gamma_elev*900/(`month'_tavg+273)*`u'*`month'_vpd)/ ///
>                                 (`month'_Delta+gamma_elev*(1+0.34*`u'))
{txt} 16{com}.                         gen `month'_ET0_Har=0.0023*(`month'_tavg+17.8)*(`month'_tmax-`month'_tmin)^0.5*0.408*`month'_Ra 
{txt} 17{com}.         local ++month_num
{txt} 18{com}.                 {c )-}
{txt}(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(30,480 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(30,480 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)

{com}.                 *Drop excess variables
.                 drop *_current_date *_J *_dr *_delta *_omega *_tavg *_vpd *_Delta *_Ra *_Rns *_Rnl *_Rn gamma_elev base_date
{txt}
{com}.         
. ********************************************************************************
. /* Create pre-season (January to April) growing-season (May to September) PRISM variables and evapotranspiration*/
. local prism_vars "ppt tmin tmax ET0_elev ET0_Har"
{txt}
{com}.         foreach pvar of local prism_vars  {c -(}
{txt}  2{com}.         local preseason_`pvar'_vars "jan_`pvar' feb_`pvar' mar_`pvar' apr_`pvar'"
{txt}  3{com}.         egen jan_april_mean_`pvar' = rmean(`preseason_`pvar'_vars')
{txt}  4{com}.         local growseason_`pvar'_vars "may_`pvar' jun_`pvar' jul_`pvar' aug_`pvar' sep_`pvar'"
{txt}  5{com}.         egen may_sep_mean_`pvar' = rmean(`growseason_`pvar'_vars')
{txt}  6{com}. {c )-}
{txt}(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(76,880 missing values generated)
(142,320 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)

{com}. 
. *Make total pre-season (January to April) growing-season (May to September) precipitation and evapotranspiration
. gen total_preseason_ppt = jan_ppt + feb_ppt + mar_ppt + apr_ppt
{txt}(76,880 missing values generated)

{com}. gen total_growseason_ppt = may_ppt + jun_ppt + jul_ppt + aug_ppt + sep_ppt
{txt}(76,880 missing values generated)

{com}. 
. local prism_vars "ET0_elev ET0_Har"
{txt}
{com}.         foreach pvar of local prism_vars  {c -(}
{txt}  2{com}.                 gen total_preseason_`pvar' = 31*jan_`pvar' + 28*feb_`pvar' + 31*mar_`pvar' + 30*apr_`pvar'
{txt}  3{com}.                 gen total_growseason_`pvar' = 31*may_`pvar' + 30*jun_`pvar' + 31*jul_`pvar' + 31*aug_`pvar' + 30*sep_`pvar'
{txt}  4{com}.         {c )-}
{txt}(142,320 missing values generated)
(142,320 missing values generated)
(107,292 missing values generated)
(107,292 missing values generated)

{com}. 
. /* Save intermediate dataset so we can merge it to the key linking wuadet_key's 
> to water right groups (WR_GROUP) */
. save "$dr_temp\wuadet_level.dta", replace
{txt}{p 0 4 2}
file {bf}
C:\Users\Micah\Dropbox\Irrigation technology transition\final revisions for conditional acceptance\replication materials\data\intermediate\wuadet_level.dta{rm}
saved
{p_end}

{com}. 
. 
. ********************************************************************************
. ********** Aggregate data to the water right group level ***********************
. ********************************************************************************
. /*
> Step 1 - Take the water right group key linking water rights groups with wuadet_key's and then
> merge in the WIMAS data with PRISM and SSURGO variables by the wuadet_key's. 
> Step 2 - Then collapse the data, as each wuadet_key appears only once, at the WR_GROUP level
> to get the aggregate water use and acres irrigated within a water right group.
> */
. use "$dr_data\raw\wrg_key.dta", clear
{txt}
{com}. keep WR_GROUP wuadet_key 
{txt}
{com}. duplicates drop

{p 0 4}{txt}Duplicates in terms of {txt} all variables{p_end}

(1,013,392 observations deleted)

{com}. 
. *Step 1 - merge with wimas
. merge 1:1 wuadet_key using "$dr_temp\wuadet_level.dta", generate(wimas_merge),
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}         286,820
{txt}{col 9}from master{col 30}{res}         210,159{txt}  (wimas_merge==1)
{col 9}from using{col 30}{res}          76,661{txt}  (wimas_merge==2)

{col 5}Matched{col 30}{res}         948,790{txt}  (wimas_merge==3)
{col 5}{hline 41}

{com}. keep if wimas_merge==3
{txt}(286,820 observations deleted)

{com}. drop wimas_merge
{txt}
{com}. drop *_merge
{txt}
{com}.         *Drop wuadet_key files that do not have irrigation as the use made of water
.         drop if umw_code!="IRR"
{txt}(30,412 observations deleted)

{com}.         *Set type_system=. if af_used_irr==0 & acres_irr==0
.         drop if af_used_irr>0 & acres_irr==0
{txt}(89 observations deleted)

{com}.         drop if acres_irr>0 & af_used_irr==0
{txt}(261,119 observations deleted)

{com}.         replace type_system=. if af_used_irr==0 & acres_irr==0
{txt}(4,537 real changes made, 4,537 to missing)

{com}. 
. *Step 2 - collapse at the WR_GROUP level
.         *First tabulate necessary categorical variables
.         foreach var of varlist type_system crop_code county gmd umw_code {c -(}
{txt}  2{com}.                 tab `var', generate(`var'_)
{txt}  3{com}.                 drop `var'
{txt}  4{com}.         {c )-}

{txt}type_system {c |}      Freq.     Percent        Cum.
{hline 12}{c +}{hline 35}
          1 {c |}{res}    109,500       17.58       17.58
{txt}          2 {c |}{res}      3,957        0.64       18.21
{txt}          3 {c |}{res}    132,229       21.23       39.44
{txt}          4 {c |}{res}    322,873       51.83       91.27
{txt}          5 {c |}{res}     12,929        2.08       93.35
{txt}          6 {c |}{res}     38,447        6.17       99.52
{txt}          7 {c |}{res}      1,535        0.25       99.77
{txt}          8 {c |}{res}      1,245        0.20       99.97
{txt}          9 {c |}{res}        210        0.03      100.00
{txt}{hline 12}{c +}{hline 35}
      Total {c |}{res}    622,925      100.00

  {txt}crop_code {c |}      Freq.     Percent        Cum.
{hline 12}{c +}{hline 35}
          0 {c |}{res}      9,684        1.52        1.52
{txt}          1 {c |}{res}     42,127        6.62        8.14
{txt}          2 {c |}{res}    222,986       35.05       43.19
{txt}          3 {c |}{res}     17,412        2.74       45.93
{txt}          4 {c |}{res}     47,769        7.51       53.44
{txt}          5 {c |}{res}     21,404        3.36       56.80
{txt}          6 {c |}{res}        875        0.14       56.94
{txt}          7 {c |}{res}        217        0.03       56.98
{txt}          8 {c |}{res}      1,166        0.18       57.16
{txt}          9 {c |}{res}      1,080        0.17       57.33
{txt}         10 {c |}{res}      2,055        0.32       57.65
{txt}         11 {c |}{res}      4,776        0.75       58.40
{txt}         12 {c |}{res}        947        0.15       58.55
{txt}         13 {c |}{res}        270        0.04       58.59
{txt}         14 {c |}{res}        525        0.08       58.68
{txt}         15 {c |}{res}     12,048        1.89       60.57
{txt}         16 {c |}{res}     95,932       15.08       75.65
{txt}         17 {c |}{res}     16,169        2.54       78.19
{txt}         18 {c |}{res}      4,480        0.70       78.90
{txt}         19 {c |}{res}      1,217        0.19       79.09
{txt}         20 {c |}{res}        928        0.15       79.23
{txt}         21 {c |}{res}      3,059        0.48       79.71
{txt}         22 {c |}{res}        956        0.15       79.86
{txt}         23 {c |}{res}      5,925        0.93       80.80
{txt}         24 {c |}{res}     28,657        4.50       85.30
{txt}         25 {c |}{res}     45,713        7.19       92.49
{txt}         26 {c |}{res}      5,219        0.82       93.31
{txt}         27 {c |}{res}      2,188        0.34       93.65
{txt}         28 {c |}{res}      6,463        1.02       94.67
{txt}         29 {c |}{res}        580        0.09       94.76
{txt}         30 {c |}{res}      5,729        0.90       95.66
{txt}         31 {c |}{res}        596        0.09       95.75
{txt}         32 {c |}{res}      2,031        0.32       96.07
{txt}         33 {c |}{res}        396        0.06       96.13
{txt}         34 {c |}{res}        532        0.08       96.22
{txt}         35 {c |}{res}      1,982        0.31       96.53
{txt}         36 {c |}{res}        239        0.04       96.57
{txt}         37 {c |}{res}        157        0.02       96.59
{txt}         38 {c |}{res}        874        0.14       96.73
{txt}         39 {c |}{res}         63        0.01       96.74
{txt}         40 {c |}{res}        222        0.03       96.77
{txt}         41 {c |}{res}         36        0.01       96.78
{txt}         42 {c |}{res}        155        0.02       96.80
{txt}         43 {c |}{res}        904        0.14       96.94
{txt}         44 {c |}{res}      6,367        1.00       97.94
{txt}         45 {c |}{res}        355        0.06       98.00
{txt}         46 {c |}{res}      4,207        0.66       98.66
{txt}         47 {c |}{res}        254        0.04       98.70
{txt}         48 {c |}{res}      2,312        0.36       99.07
{txt}         49 {c |}{res}        931        0.15       99.21
{txt}         50 {c |}{res}         69        0.01       99.22
{txt}         51 {c |}{res}        231        0.04       99.26
{txt}         52 {c |}{res}        193        0.03       99.29
{txt}         53 {c |}{res}        109        0.02       99.31
{txt}         54 {c |}{res}        285        0.04       99.35
{txt}         55 {c |}{res}         23        0.00       99.35
{txt}         56 {c |}{res}        112        0.02       99.37
{txt}         57 {c |}{res}         13        0.00       99.37
{txt}         58 {c |}{res}        176        0.03       99.40
{txt}         59 {c |}{res}        143        0.02       99.42
{txt}         60 {c |}{res}          9        0.00       99.43
{txt}         61 {c |}{res}         77        0.01       99.44
{txt}         62 {c |}{res}          9        0.00       99.44
{txt}         63 {c |}{res}        653        0.10       99.54
{txt}         64 {c |}{res}         29        0.00       99.55
{txt}         65 {c |}{res}        286        0.04       99.59
{txt}         66 {c |}{res}        129        0.02       99.61
{txt}         67 {c |}{res}         23        0.00       99.62
{txt}         68 {c |}{res}         26        0.00       99.62
{txt}         69 {c |}{res}         12        0.00       99.62
{txt}         70 {c |}{res}         10        0.00       99.62
{txt}         71 {c |}{res}          7        0.00       99.62
{txt}         72 {c |}{res}         16        0.00       99.63
{txt}         73 {c |}{res}         36        0.01       99.63
{txt}         74 {c |}{res}         20        0.00       99.64
{txt}         75 {c |}{res}        342        0.05       99.69
{txt}         76 {c |}{res}        316        0.05       99.74
{txt}         77 {c |}{res}      1,355        0.21       99.95
{txt}         78 {c |}{res}         25        0.00       99.96
{txt}         79 {c |}{res}        272        0.04      100.00
{txt}         80 {c |}{res}          5        0.00      100.00
{txt}         82 {c |}{res}          1        0.00      100.00
{txt}         85 {c |}{res}          1        0.00      100.00
{txt}         88 {c |}{res}          1        0.00      100.00
{txt}         92 {c |}{res}          1        0.00      100.00
{txt}{hline 12}{c +}{hline 35}
      Total {c |}{res}    636,184      100.00

     {txt}county {c |}      Freq.     Percent        Cum.
{hline 12}{c +}{hline 35}
         AT {c |}{res}        161        0.02        0.02
{txt}         BA {c |}{res}      1,245        0.19        0.21
{txt}         BR {c |}{res}        157        0.02        0.24
{txt}         BT {c |}{res}     10,659        1.62        1.86
{txt}         BU {c |}{res}        297        0.05        1.90
{txt}         CA {c |}{res}      1,112        0.17        2.07
{txt}         CD {c |}{res}      7,145        1.09        3.16
{txt}         CF {c |}{res}         45        0.01        3.17
{txt}         CK {c |}{res}         72        0.01        3.18
{txt}         CL {c |}{res}        631        0.10        3.28
{txt}         CM {c |}{res}      1,825        0.28        3.55
{txt}         CN {c |}{res}     12,911        1.96        5.52
{txt}         CR {c |}{res}         29        0.00        5.52
{txt}         CY {c |}{res}      5,843        0.89        6.41
{txt}         DC {c |}{res}      5,695        0.87        7.28
{txt}         DG {c |}{res}        861        0.13        7.41
{txt}         DK {c |}{res}      1,186        0.18        7.59
{txt}         DP {c |}{res}        225        0.03        7.62
{txt}         ED {c |}{res}     23,640        3.60       11.22
{txt}         EL {c |}{res}      1,513        0.23       11.45
{txt}         EW {c |}{res}        157        0.02       11.47
{txt}         FI {c |}{res}     44,253        6.73       18.21
{txt}         FO {c |}{res}     19,823        3.02       21.23
{txt}         FR {c |}{res}         45        0.01       21.23
{txt}         GE {c |}{res}        830        0.13       21.36
{txt}         GH {c |}{res}      4,173        0.63       21.99
{txt}         GL {c |}{res}      5,556        0.85       22.84
{txt}         GO {c |}{res}      7,404        1.13       23.97
{txt}         GT {c |}{res}     16,138        2.46       26.42
{txt}         GW {c |}{res}         13        0.00       26.42
{txt}         GY {c |}{res}     35,766        5.44       31.87
{txt}         HG {c |}{res}      9,791        1.49       33.36
{txt}         HM {c |}{res}      5,870        0.89       34.25
{txt}         HP {c |}{res}        645        0.10       34.35
{txt}         HS {c |}{res}     25,406        3.87       38.21
{txt}         HV {c |}{res}      9,498        1.45       39.66
{txt}         JA {c |}{res}         16        0.00       39.66
{txt}         JF {c |}{res}      1,474        0.22       39.88
{txt}         JO {c |}{res}        134        0.02       39.91
{txt}         JW {c |}{res}        629        0.10       40.00
{txt}         KE {c |}{res}     19,776        3.01       43.01
{txt}         KM {c |}{res}      4,206        0.64       43.65
{txt}         KW {c |}{res}     11,744        1.79       45.44
{txt}         LB {c |}{res}          2        0.00       45.44
{txt}         LC {c |}{res}        269        0.04       45.48
{txt}         LE {c |}{res}      5,746        0.87       46.35
{txt}         LG {c |}{res}      2,801        0.43       46.78
{txt}         LV {c |}{res}        280        0.04       46.82
{txt}         LY {c |}{res}         46        0.01       46.83
{txt}         MC {c |}{res}        562        0.09       46.91
{txt}         ME {c |}{res}     17,228        2.62       49.54
{txt}         MN {c |}{res}        740        0.11       49.65
{txt}         MP {c |}{res}      9,518        1.45       51.10
{txt}         MR {c |}{res}         51        0.01       51.10
{txt}         MS {c |}{res}        337        0.05       51.16
{txt}         MT {c |}{res}      7,912        1.20       52.36
{txt}         NM {c |}{res}         51        0.01       52.37
{txt}         NO {c |}{res}          9        0.00       52.37
{txt}         NS {c |}{res}      2,202        0.34       52.70
{txt}         NT {c |}{res}      5,173        0.79       53.49
{txt}         OB {c |}{res}      1,607        0.24       53.74
{txt}         OS {c |}{res}         13        0.00       53.74
{txt}         OT {c |}{res}      1,543        0.23       53.97
{txt}         PL {c |}{res}      3,907        0.59       54.57
{txt}         PN {c |}{res}     19,454        2.96       57.53
{txt}         PR {c |}{res}     18,532        2.82       60.35
{txt}         PT {c |}{res}      3,891        0.59       60.94
{txt}         RA {c |}{res}      5,955        0.91       61.85
{txt}         RC {c |}{res}      6,427        0.98       62.82
{txt}         RH {c |}{res}      5,736        0.87       63.70
{txt}         RL {c |}{res}      1,622        0.25       63.94
{txt}         RN {c |}{res}     14,406        2.19       66.13
{txt}         RO {c |}{res}      1,251        0.19       66.33
{txt}         RP {c |}{res}      7,909        1.20       67.53
{txt}         RS {c |}{res}         47        0.01       67.54
{txt}         SA {c |}{res}      2,076        0.32       67.85
{txt}         SC {c |}{res}     16,589        2.52       70.38
{txt}         SD {c |}{res}     19,248        2.93       73.31
{txt}         SF {c |}{res}     20,024        3.05       76.35
{txt}         SG {c |}{res}     14,004        2.13       78.48
{txt}         SH {c |}{res}     23,804        3.62       82.11
{txt}         SM {c |}{res}      2,181        0.33       82.44
{txt}         SN {c |}{res}      5,355        0.81       83.25
{txt}         ST {c |}{res}     15,033        2.29       85.54
{txt}         SU {c |}{res}      2,531        0.39       85.92
{txt}         SV {c |}{res}     19,067        2.90       88.83
{txt}         SW {c |}{res}     15,661        2.38       91.21
{txt}         TH {c |}{res}     21,705        3.30       94.51
{txt}         TR {c |}{res}      2,087        0.32       94.83
{txt}         WA {c |}{res}     11,301        1.72       96.55
{txt}         WB {c |}{res}      1,891        0.29       96.84
{txt}         WH {c |}{res}     18,637        2.84       99.67
{txt}         WL {c |}{res}          2        0.00       99.67
{txt}         WS {c |}{res}      2,038        0.31       99.98
{txt}         WY {c |}{res}        110        0.02      100.00
{txt}{hline 12}{c +}{hline 35}
      Total {c |}{res}    657,170      100.00

        {txt}gmd {c |}      Freq.     Percent        Cum.
{hline 12}{c +}{hline 35}
          1 {c |}{res}     55,554       10.44       10.44
{txt}          2 {c |}{res}     37,505        7.05       17.49
{txt}          3 {c |}{res}    238,006       44.72       62.21
{txt}          4 {c |}{res}     90,100       16.93       79.14
{txt}          5 {c |}{res}    111,018       20.86      100.00
{txt}{hline 12}{c +}{hline 35}
      Total {c |}{res}    532,183      100.00

   {txt}umw_code {c |}      Freq.     Percent        Cum.
{hline 12}{c +}{hline 35}
        IRR {c |}{res}    657,170      100.00      100.00
{txt}{hline 12}{c +}{hline 35}
      Total {c |}{res}    657,170      100.00
{txt}
{com}. 
.         *Collapse 
.                 collapse (sum) af_used af_used_irr acres_irr corn_acres soy_acres flood_acres ///
>                                         cp_acres lepa_acres ///
>                                  (mean) hyd_cond sy predev_dtw predev_sat dpth_water dpth_well ///
>                                         longitude latitude slope-musumcpct jan_tmin-dec_ppt ///
>                                         jan_ET0_elev-total_growseason_ET0_Har type_system* ///
>                                         crop_code* county* gmd* umw_code*, by(WR_GROUP wua_year)
{res}{txt}
{com}.                                 
. *Step 3 - Merge authorized quantites and authorized acreage for water right groups
.         *Start with authorized quantities
.         merge m:1 WR_GROUP using "$dr_data\raw\wrg_auth_quant.dta", generate(auth_quant_merge)
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}          12,204
{txt}{col 9}from master{col 30}{res}          10,198{txt}  (auth_quant_merge==1)
{col 9}from using{col 30}{res}           2,006{txt}  (auth_quant_merge==2)

{col 5}Matched{col 30}{res}         434,480{txt}  (auth_quant_merge==3)
{col 5}{hline 41}

{com}.                 drop if auth_quant_merge==2
{txt}(2,006 observations deleted)

{com}.         merge m:1 WR_GROUP using "$dr_data\raw\wrg_auth_acres.dta", generate(auth_acres_merge)
{res}
{txt}{col 5}Result{col 33}Number of obs
{col 5}{hline 41}
{col 5}Not matched{col 30}{res}             568
{txt}{col 9}from master{col 30}{res}               0{txt}  (auth_acres_merge==1)
{col 9}from using{col 30}{res}             568{txt}  (auth_acres_merge==2)

{col 5}Matched{col 30}{res}         444,678{txt}  (auth_acres_merge==3)
{col 5}{hline 41}

{com}.                 drop if auth_acres_merge==2
{txt}(568 observations deleted)

{com}.         drop auth_quant_merge auth_acres_merge
{txt}
{com}.         
. *Step 4 - Make ssurgo and predevelopment aquifer variables time invariant
.         foreach var of varlist hyd_cond sy predev_dtw predev_sat sandtotal silttotal awc slope {c -(}
{txt}  2{com}.                 bysort WR_GROUP: egen mean_`var' = mean(`var')
{txt}  3{com}.                 replace `var' = mean_`var'
{txt}  4{com}.                 drop mean_`var'
{txt}  5{com}.         {c )-}
{txt}(70,993 missing values generated)
(24,712 real changes made)
(70,993 missing values generated)
(14,949 real changes made)
(70,993 missing values generated)
(27,317 real changes made)
(70,993 missing values generated)
(27,239 real changes made)
(1,460 missing values generated)
(433,358 real changes made)
(1,460 missing values generated)
(440,852 real changes made)
(1,460 missing values generated)
(443,218 real changes made)
(1,411 missing values generated)
(121,501 real changes made)

{com}. 
. *Save
.         save "$dr_temp\wrg_collapsed.dta", replace
{txt}{p 0 4 2}
file {bf}
C:\Users\Micah\Dropbox\Irrigation technology transition\final revisions for conditional acceptance\replication materials\data\intermediate\wrg_collapsed.dta{rm}
saved
{p_end}

{com}.         
. *Close log 
.         log close
      {txt}name:  {res}<unnamed>
       {txt}log:  {res}C:\Users\Micah\Dropbox\Irrigation technology transition\final revisions for conditional acceptance\replication materials\outputs\logs\prepare_wrg_dataset.smcl
  {txt}log type:  {res}smcl
 {txt}closed on:  {res}19 May 2024, 20:21:14
{txt}{.-}
{smcl}
{txt}{sf}{ul off}